ALTER PROCEDURE [dbo].[spContract_SetStatus]
(
	@ContractId int,
	@Status varchar(50),
	
	@ContractLinkValidDays int = null,
	@IP varchar(20) = null,
	@Pin varchar(4) out
)
AS
BEGIN
	set nocount on
	set xact_abort on
	
	if (@Status = 'Sent')
	begin
		declare @LinkCode varchar(255), @ExpiredLinkDate datetime
		set @LinkCode = CONVERT(varchar(255), NEWID())
		set @ExpiredLinkDate = DATEADD(day, @ContractLinkValidDays, convert(date, GETDATE()))
		
		-- if first code generation
		if exists(select 1 from [Contract] where (ContractId = @ContractId) and (LinkCode is null))
			update [Contract]
			set [Status] = @Status, ContractDate = CONVERT(date, getdate()),
				LinkCode = @LinkCode, ExpiredLinkDate = @ExpiredLinkDate
			where ContractId = @ContractId
		else
			update [Contract]
			set LinkCode = @LinkCode, ExpiredLinkDate = @ExpiredLinkDate
			where ContractId = @ContractId
	end
	
	if (@Status = 'Viewed')
	begin
		if exists(select 1 from [Contract] where (ContractId = @ContractId) and (ViewDate is null))
			update [Contract]
			set [Status] = @Status,
				ViewDate = GETDATE(), ViewIP = @IP
			where ContractId = @ContractId
	end
	
	if (@Status = 'Signed')
	begin
		if exists(select 1 from [Contract] where (ContractId = @ContractId) and (SignedDate is null))
		begin
			declare @PackageId int, @ContactId int, @ExpDate datetime
			
			select @PackageId = PackageId, @ContactId = ContactId, @ExpDate = ExpDate
			from [Contract] (nolock)
			where ContractId = @ContractId
			
			if exists(select 1 from Contact (nolock) where (ContactId = @ContactId) and (Pin is null))
				exec spPin_Create @Pin = @Pin out
						
			begin transaction
				update [Contract]
				set [Status] = @Status,
					SignedDate = GETDATE(), SignedIP = @IP
				where ContractId = @ContractId
				
				-- if pin was generated, than write it
				if @Pin is not null
					update Contact
					set Pin = @Pin
					where (ContactId = @ContactId)
				
				if (@PackageId is not null)
				begin
					insert into ClientPackage
					(ClientId, [Status], StartDate, ExpDate, PackageId)
					values(@ContactId, 'AVAILABLE', GETDATE(), @ExpDate, @PackageId)
				end
			commit transaction
		end
	end
END
go